package com.dajingling.dao.impl;

import com.dajingling.bean.EUserExpress;
import com.dajingling.bean.UserAdmin;
import com.dajingling.dao.BaseEUserExpressDao;
import com.dajingling.exception.DuplicateCodeException;
import com.dajingling.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EUserExpressDaoMysql implements BaseEUserExpressDao {

    /*
    查询：快递员总数量，新增快递员总数量，
     */
    public static final String SQL_CONSOLE = "select count(uid) size,count(to_days(logontime)=to_days(now()) or null) today from e_userexpress";
    /*
    查询数据库所有快递员信息
     */
    public static final String SQL_FIND_ALL = "select * from e_userexpress";
    /*
    分页查询数据库所有快递员信息
     */
    public static final String SQL_FIND_LIMIT = "select * from e_userexpress limit ?,?";
    /*
    根据手机号查询数据库快递员信息
     */
    public static final String SQL_FIND_BY_USERPHONE = "select * from e_userexpress where userphone=?";
    /*
    快递员录入
     */
    public static final String SQL_INSERT = "insert into e_userexpress(username,password,userphone,idcard,logontime) values(?,?,?,?,now())";
    /*
    快递员修改
    update useradmin set username=?,userphone=?,idcard=?,password=? where userphone=?;
     */
    public static final String SQL_UPDATE = "update e_userexpress set username=?,userphone=?,idcard=?,password=? where userphone=?";
    /*
    快递员删除
     */
    public static final String SQL_DELETE = "delete from e_userexpress where userphone=?";


    /**
     * 查询数据库全部快递员（总数，新增）
     *
     * @return {size:总数,day:新增}
     */
    @Override
    public Map<String, Integer> console() {
        Map data = new HashMap();
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_CONSOLE);
            //3.填充参数（可选）
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            if (resultSet.next()){
                int size = resultSet.getInt(1);
                int today = resultSet.getInt(2);
                data.put("data_size",size);
                data.put("data_day",today);
            }
        }catch (Exception e) {
            System.out.println("查询快递数量失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return data;
    }

    /**
     * 查询所有快递员
     *
     * @param limit      分页：true，查询所有快递员信息：false
     * @param offset     SQL的起始索引
     * @param pageNumber 页查询数量
     * @return 快递员列表
     */
    @Override
    public List<EUserExpress> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<EUserExpress> list = new ArrayList();
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            if (limit){
                statement = connection.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数（可选）
                statement.setInt(1,offset);
                statement.setInt(2,pageNumber);
            }else {
                statement = connection.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            while (resultSet.next()){
                int uid = resultSet.getInt(1);
                String userName = resultSet.getString(2);
                String password = resultSet.getString(3);
                String userPhone = resultSet.getString(4);
                String idCard = resultSet.getString(5);
                int expressNumber = resultSet.getInt(6);
                Timestamp logonTime = resultSet.getTimestamp(7);
                Timestamp loginTime = resultSet.getTimestamp(8);
                list.add(new EUserExpress(uid, userName, password, userPhone, idCard, expressNumber, logonTime, loginTime,null));
            }
        }catch (Exception e) {
            System.out.println("查询快递员列表失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return list;
    }

    /**
     * 根据快递员的手机号，查询快递员信息
     *
     * @param userPhone 电话号码
     * @return 快递员类，不存在：null
     */
    @Override
    public EUserExpress findByUserPhone(String userPhone) {
        EUserExpress eUserExpress = null;
        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.填充参数（可选）
            statement.setString(1,userPhone);
            //4.执行SQL语句
            resultSet = statement.executeQuery();
            //5.获取执行结果
            if (resultSet.next()){
                int uid = resultSet.getInt(1);
                String userName = resultSet.getString(2);
                String password = resultSet.getString(3);
                String idCard = resultSet.getString(5);
                int expressNumber = resultSet.getInt(6);
                Timestamp logonTime = resultSet.getTimestamp(7);
                Timestamp loginTime = resultSet.getTimestamp(8);
                eUserExpress = new EUserExpress(uid, userName, password, userPhone, idCard, expressNumber, logonTime, loginTime,null);
            }
        }catch (Exception e) {
            System.out.println("查询用户信息失败！！！");
        }finally {
            //6.资源的释放
            DruidUtil.close(connection,statement,resultSet);
        }
        return eUserExpress;
    }

    /**
     * 快递员录入
     *
     * @param eUserExpress 要录入的快递员对象
     * @return true，false
     */
    @Override
    public boolean insert(EUserExpress eUserExpress) {
        //1.连接的获取
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //2.预编译SQL语句
            statement = connection.prepareStatement(SQL_INSERT);
            //3.填充参数
            //insert into useradmin(username,password,userphone,idcard,logontime) values(?,?,?,?,now())
            statement.setString(1,eUserExpress.getUserName());
            statement.setString(2,eUserExpress.getPassword());
            statement.setString(3,eUserExpress.getUserPhone());
            statement.setString(4,eUserExpress.getIdCard());
            //4.执行SQL语句，并获取执行结果
            return statement.executeUpdate()>0;
        } catch (SQLException e1) {
            System.out.println("快递员录入失败！");
        }finally {
            //5.释放资源
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }

    /**
     * 根据电话，快递员信息修改
     *
     * @param userPhone       快递员号码
     * @param newEUserExpress 新的快递员对象（username，password，userPhone，idcard）
     * @return true，false
     */
    @Override
    public boolean update(String userPhone, EUserExpress newEUserExpress) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_UPDATE);
            statement.setString(1,newEUserExpress.getUserName());
            statement.setString(2,newEUserExpress.getUserPhone());
            statement.setString(3,newEUserExpress.getIdCard());
            statement.setString(4,newEUserExpress.getPassword());
            statement.setString(5,userPhone);
            return statement.executeUpdate()>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }

    /**
     * 根据userPhone，快递员删除
     *
     * @param userPhone 快递员手机号
     * @return true，false
     */
    @Override
    public boolean delete(String userPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_DELETE);
            statement.setString(1,userPhone);
            return statement.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,statement,null);
        }
        return false;
    }
}
